SQL performance is a very critical component of the overall performance of a database system. Although database applications are tested and verified before delivery to customers, the performance of SQL statements in the database applications is difficult to predict. Every time an SQL statement is executed, a different execution plan may be generated by the query optimizer. Often, the execution plan is one that has not been tested before and one whose performance has not been analyzed and verified. Should the query optimizer choose a plan that does not give the best performance, the performance of the system as a whole may suffer.
SQL tuning is a process where the performance of certain SQL statements is analyzed and changes are implemented in an effort to improve the performance of the SQL statements. However, SQL tuning is complex and time-consuming, requiring expertise in query optimization, access design, and SQL design. Often, this difficult task is performed manually by the database administrator (DBA), who must also re-tune SQL statements as the workload set on the database system and the database system itself change over time.
An SQL Tuning Advisor, as described in “AUTOMATIC SQL TUNING ADVISOR”, application Ser. No. 10/936,778, filed Sep. 7, 2004, the entire contents of which is hereby incorporated by reference as if fully set forth herein, provides the DBA with the functionality of tuning SQL statements by generating tuning recommendations for the SQL statements. The SQL Tuning Advisor can, for example, perform access path analysis and recommend creating new indexes, perform statement structure analysis and recommend better written statements, and perform data statistics analysis and recommend replacing missing or stale data statistics with updated statistics.
However, using the SQL Tuning Advisor still requires significant time, effort, and involvement from DBAs. In order to use the SQL Tuning Advisor, DBAs are required to find SQL statements that are exhibiting poor performance as candidates for tuning, feed the candidate SQL statements into the SQL Tuning Advisor, and manually evaluate the results and tuning recommendations generated by the SQL Tuning Advisor to decide which tuning recommendations to implement. The SQL tuning process implemented by the SQL Tuning Advisor is itself time-consuming and, on a busy system, may need to be scheduled by the DBA. Furthermore after deciding which tuning recommendations to implements, DBAs must also monitor the performance of the database system after implementation of the tuning recommendations to check that performance gains have been achieved according to expectations. If the performance has instead deteriorated, DBAs must then perform further analysis to respond to the problem. To prepare for such a situation, DBAs also need to keep a record of prior tuning actions. Finally, this tuning process must be repeated periodically because both workload sets and database systems may change over time.
Thus, there is a need for a fully automated process for tuning SQL statements that require minimal time and effort from database administrators.
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.